﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Softpro.AccesoDatosDB2;

namespace Consulta.Manager
{
    public class MTercero
    {
        public DataTable ConsultarLista(string pFiltro, string pClausulaOrderBy)
        {

            try
            {
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pFiltro!= null && pFiltro.Length > 0)
                {
                    string tSql;
                    tSql = "SELECT FNOMBT As Nombre," +
                        " FNITAL AS Nit," +
                        " FDIREC AS Direccion," +
                        " FTELEF AS Telefono," +
                        " FESTAD AS Estado, " +
                        " COALESCE(T2.ACRNIT, 0) As NitCXP " +
                        " FROM CNBEDP.CNNITEDP T1 " +
                        " LEFT OUTER JOIN CPXDATOS.ACRBAS T2 ON (T1.FNITAL = T2.ACRNIT) " +
                        " WHERE FNOMBT Like '%" + pFiltro.ToUpper() + "%' ";
                    if (pClausulaOrderBy.Length > 0)
                    {
                        tSql += " ORDER BY " + pClausulaOrderBy;
                    }
                    else
                    {
                        tSql += " ORDER BY Nombre";
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable ConsultarPagos(string pFiltro, string pDesde, string pHasta, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    DateTime tDesde = Convert.ToDateTime(pDesde);
                    DateTime tHasta = Convert.ToDateTime(pHasta);
                    accesoDatos p = new accesoDatos();
                    tSql = crearConsultaPago(pFiltro, tDesde, tHasta, pClausulaOrderBy);
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable ConsultarVencimientos(string pFiltro, string pDesde, string pHasta, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    DateTime tDesde = Convert.ToDateTime(pDesde);
                    DateTime tHasta = Convert.ToDateTime(pHasta);
                    accesoDatos p = new accesoDatos();
                    tSql = crearConsultaVencimiento(pFiltro, tDesde, tHasta, pClausulaOrderBy);
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        private string crearConsultaPago(string pFiltro, DateTime tDesde, DateTime tHasta, string pClausulaOrderBy)
        {
            string tCampos = " T1.EGRDSN AS Acreedor," +
                        " T1.EGRNIT AS Nit," +
                        " T1.EGRCMP As Comprobante," +
                        " T1.EGRFCH AS Fecha," +
                        " T1.EGRTIPPAG AS Tipo," +
                        " T1.EGRNDC AS Documento," +
                        " CASE (T1.EGRMON) " +
                        "   WHEN 'US' then 'Dolares' " +
                        "   ELSE 'Colones' " +
                        " END AS Moneda," +
                        " T1.EGRVNT AS Valor," +
                        " T1.EGRCon AS Conc," +
                        " T2.CTCDSC As Cuenta ";
            string tCondicion = " WHERE " +
                        " T1.EGRCTC = T2.CTCCTC " +
                        " AND (T1.EGRSTS = '6' OR T1.EGRSTS = '7') " +
                        " AND T1.EGRNIT = " + pFiltro +
                        " AND T1.EGRFCH between '" + tDesde.ToString("yyyyMMdd") + "' And '" + tHasta.ToString("yyyyMMdd") + "' ";
            string tSql = "SELECT 'BAC' as Cia, " + tCampos +
                        " FROM " +
                        " CPXDTCRP.EGRBAS As T1, " +
                        " CPXDTCRP.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'TER' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTTEP.EGRBAS As T1, " +
                        " CPXDTTEP.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'AGC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTAGC.EGRBAS As T1, " +
                        " CPXDTAGC.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'FTV' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTFTV.EGRBAS As T1, " +
                        " CPXDTFTV.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'INC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTINC.EGRBAS As T1, " +
                        " CPXDTINC.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'BAN' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTBAN.EGRBAS As T1, " +
                        " CPXDTBAN.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'CAY' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTCAY.EGRBAS As T1, " +
                        " CPXDTCAY.CTCBAS As T2" +
                        tCondicion;
            if (pClausulaOrderBy.Length > 0)
            {
                tSql += " ORDER BY " + pClausulaOrderBy;
            }
            else
            {
                tSql += " ORDER BY Fecha";
            }
            return tSql;
        }
        private string crearConsultaVencimiento(string pFiltro, DateTime tDesde, DateTime tHasta, string pClausulaOrderBy)
        {
            string tCampos = " T2.OBLDSN As Acreedor," +
                        "  T2.OBLNIT As Nit," +
                        "  T2.OBLNDC AS Factura," +
                        "  T2.OBLFCH As Fecha," +
                        "  T2.OBLFVC As Vencimiento," +
                        "  SUBSTR(T2.OBLFVC,1,4) As Anno," +
                        "  T3.CBNSEM As Semana," +
                        "  T2.OBLMON As Moneda," +
                        "  T2.OBLVNT As Valor," +
                        "  T2.OBLVAP As Abonos," +
                        "  (T2.OBLVNT - T2.OBLVAP) As Saldo," +
                        "  T2.OblObs As Observacion";
            string tCondicion = " WHERE " +
                        " T3.CBNFDS <= T2.OBLFVC" +
                        " AND T3.CBNFHS >= T2.OBLFVC" +
                        " AND T2.OBLNIT = " + pFiltro +
                        " AND T2.OBLFVC BETWEEN '" + tDesde.ToString("yyyyMMdd") + "' And '" + tHasta.ToString("yyyyMMdd") + "' " +
                        " AND T2.OBLSTS = '2'";
            string tSql = "SELECT 'BAC' as Cia, " + tCampos +
                        " FROM " +
                        " CPXDTCRP.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'TER' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTTEP.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'AGC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTAGC.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'FTV' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTFTV.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'INC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTINC.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'BAN' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTBAN.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'CAY' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTCAY.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion;
            if (pClausulaOrderBy.Length > 0)
            {
                tSql += " ORDER BY " + pClausulaOrderBy;
            }
            else
            {
                tSql += " ORDER BY Fecha";
            }
            return tSql;

        }
    }
}
